package main

import (
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

func main() {

	// connect to mysql
	db, err := sql.Open("mysql", "root:vader20011014@(localhost:3306)/study?parseTime=true")

	if err != nil {
		log.Fatal(err)
	}

	if err := db.Ping(); err != nil {
		log.Fatal(err)
	}
	fmt.Println("connected!")

	{
		// create a new table
		query := `
            CREATE TABLE IF NOT EXISTS users(
                id INT AUTO_INCREMENT,
                username TEXT NOT NULL,
                password TEXT NOT NULL,
                created_at DATETIME,
                PRIMARY KEY (id)
            );`
		
		if _, err := db.Exec(query); err != nil {
			log.Fatal(err)
		}
	}

	{
		// insert a user
		username := "root"
		password := "6666"
		create_at := time.Now()

		query := `INSERT INTO users (username,password,created_at) VALUES (?,?,?)`

		res, err := db.Exec(query, username, password, create_at)
		if err != nil {
			log.Fatal(err)
		}

		id, err := res.LastInsertId()
		fmt.Println(id)
	}

	{
		// query a single user
		var (
			id        int
			username  string
			password  string
			createAt time.Time
		)

		query := `SELECT id,username,password,created_at FROM users where id=?`
		if err := db.QueryRow(query,1).Scan(&id,&username,&password,&createAt); err != nil{
			log.Fatal(err)
		}

		fmt.Println(id,username,password,createAt)
	}

	{
		// query all users
		type user struct{
			id        int
			username  string
			password  string
			createAt time.Time
		}
		
		query := `SELECT * FROM users`
		rows,err := db.Query(query)
		if err != nil {
			log.Fatal(err)
		}
		defer rows.Close()

		var users []user
		for rows.Next(){
			var u user
			err := rows.Scan(&u.id,&u.username,&u.password,&u.createAt)
			if err != nil{
				log.Fatal(err)
			}
			users = append(users, u)
		}
		if err := rows.Err();err != nil {
			log.Fatal(err)
		}

		fmt.Printf("%#v\n",users)

	}

	{
		// delete a user
        _, err := db.Exec(`DELETE FROM users WHERE id = ?`, 1)
        if err != nil {
            log.Fatal(err)
        }
	}
}
